package io.potato.ts.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import io.potato.ts.domain.Contacts;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * 通讯录--联系人表(t_contacts)数据访问接口
 * 
 * @author timl
 *
 * <p>2019-01-07 15:13:58</p>
 */
public interface ContactsRepository extends JpaRepository<Contacts, Integer> {
	
	@Query(value = "select name from t_contacts " +
			"where user_id = :userId AND mobile_no = :mobileNo limit 1", nativeQuery = true)
	String findNameByMobileNo(@Param("userId") Integer userId, @Param("mobileNo") String mobileNo);

	@Query( nativeQuery = true,
			value="select t.* from t_contacts t " +
					"join t_contacts_link t1 on t.id = t1.contracts_id " +
					"where t.user_id = :userId " +
					"AND t1.group_id IN (:groupId) " +
					"AND (t.mobile_no like binary concat('%', :mobileNo ,'%')  " +
					" OR t.name like binary concat('%', :mobileNo ,'%'))" +
					"order by t.show_order ASC, t.name  ")
	List<Contacts> findByGroupId(@Param("userId") Integer userId,
								 @Param("groupId") List<Integer> groupId,
								 @Param("mobileNo") String mobileNo);

	@Query( nativeQuery = true,
			value="select t.* from t_contacts t " +
					"where t.user_id = :userId " +
					"AND (t.mobile_no like binary concat('%', :mobileNo ,'%')  " +
					" OR t.name like binary concat('%', :mobileNo ,'%'))" +
					"order by t.show_order ASC, t.name ")
	List<Contacts> findByUserId(@Param("userId") Integer userId,
								 @Param("mobileNo") String mobileNo);

	@Query( nativeQuery = true,
			value="select t.* from t_contacts t " +
					"join t_contacts_link t1 on t.id = t1.contracts_id " +
					"where t.organ_id = :organId " +
					"AND t1.group_id  IN (:groupId) " +
					"AND (t.mobile_no like binary concat('%', :mobileNo ,'%')  " +
					" OR t.name like binary concat('%', :mobileNo ,'%'))" +
					"order by t.show_order ASC, t.name  ")
	List<Contacts> findShareByGroupId(@Param("organId") Integer organId,
								 @Param("groupId") List<Integer> groupId,
								 @Param("mobileNo") String mobileNo);

	@Query( nativeQuery = true,
			value="select t.* from t_contacts t " +
					"where t.organ_id = :organId AND type = 2 " +
					"AND (t.mobile_no like binary concat('%', :mobileNo ,'%')  " +
					" OR t.name like binary concat('%', :mobileNo ,'%'))" +
					"order by t.show_order ASC, t.name  ")
	List<Contacts> findShareByOrganId(@Param("organId") Integer organId,
									  @Param("mobileNo") String mobileNo);

	@Modifying
	@Query(nativeQuery = true,
			value = "delete from t_contacts where id in (:idList) AND not exists (select * from t_contacts_link where contracts_id = t_contacts.id)"
	)
	void deleteUnused(@Param("idList") List<Integer> idList);

	@Modifying
	@Query(nativeQuery = true,
			value = "delete from t_contacts where " +
					"id in (select group_id from t_contacts_link where group_id = :groupId) " +
					"AND not exists (select * from t_contacts_link where contracts_id = t_contacts.id)"
	)
	void deleteUnused(@Param("groupId") Integer groupId);

	@Modifying
	@Query("delete from Contacts where userId = :userId")
	void deleteByUserId(@Param("userId") Integer userId);
	
}
